package birs.controller.staff.task;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import birs.model.staff.STF002Model;
import birs.model.staff.STF002ModelA;
import birs.model.staff.STF002ModelB;
import birs.util.ConnectionDB;
import birs.util.ConvertDataType;
import birs.util.ResultSetMapper;

public class STF002TaskA {
	
	public void getStaffAmountByFaculty(HttpServletRequest request,HttpServletResponse response){
		try (Connection con = ConnectionDB.getMisConnection()){
			ResultSetMapper<STF002Model> stf002Model = new ResultSetMapper<STF002Model>();
			List<STF002Model> stf002ModelList = stf002Model.mapRersultSetToObject(getStaffAmountByFacultySQL(con), STF002Model.class);
			String resultJson = ConvertDataType.getInstance().objectToJasonArray(stf002ModelList);
			sendResponse(request, response, resultJson);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public void getFirstDrilldown(HttpServletRequest request,HttpServletResponse response){
		try (Connection con = ConnectionDB.getMisConnection()){
			ResultSetMapper<STF002ModelA> stf002ModelA = new ResultSetMapper<STF002ModelA>();
			List<STF002ModelA> stf002ModelAList = stf002ModelA.mapRersultSetToObject(getFirstDrilldownSQL(con, request.getParameter("departmentCode")), STF002ModelA.class);
			String resultJson = ConvertDataType.getInstance().objectToJasonArray(stf002ModelAList);
			sendResponse(request, response, resultJson);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public void getSecondDrilldown(HttpServletRequest request,HttpServletResponse response){
		try (Connection con = ConnectionDB.getMisConnection()){
			String departmentCode = request.getParameter("departmentCode");
			String staffType = request.getParameter("staffType");
			ResultSetMapper<STF002ModelB> stf002ModelB = new ResultSetMapper<STF002ModelB>();
			List<STF002ModelB> stf002ModelBList = stf002ModelB.mapRersultSetToObject(getSecondDrilldownSQL(con, departmentCode, staffType), STF002ModelB.class);
			String resultJson = ConvertDataType.getInstance().objectToJasonArray(stf002ModelBList);
			sendResponse(request, response, resultJson);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	private ResultSet getStaffAmountByFacultySQL(Connection con){
		String sql = " 	SELECT '1000000 AND 1999999' AS DEPARTMENTCODE,'สำนักอธิการบดี' AS DEPARTMENTNAME,COUNT(1) AS AMOUNT	"
				+" 	FROM V_HRM_STAFFEDU_HIGHEST V	"
				+" 	WHERE TO_NUMBER(V.DEPARTMENTCODE) BETWEEN 1000000 AND 1999999	"
				+" 		"
				+" 	UNION	"
				+" 		"
				+" 	SELECT '2000000 AND 2999999' AS DEPARTMENTCODE,'สำนักวิทยบริการและเทคโนโลยี' AS DEPARTMENTNAME,COUNT(1) AS AMOUNT	"
				+" 	FROM V_HRM_STAFFEDU_HIGHEST V	"
				+" 	WHERE TO_NUMBER(V.DEPARTMENTCODE) BETWEEN 2000000 AND 2999999	"
				+" 		"
				+" 	UNION	"
				+" 		"
				+" 	SELECT '3000000 AND 3999999' AS DEPARTMENTCODE,'สำนักศิลปวัฒนธรรมและพัฒนาชุมชน' AS DEPARTMENTNAME,COUNT(1) AS AMOUNT	"
				+" 	FROM V_HRM_STAFFEDU_HIGHEST V	"
				+" 	WHERE TO_NUMBER(V.DEPARTMENTCODE) BETWEEN 3000000 AND 3999999	"
				+" 		"
				+" 	UNION	"
				+" 		"
				+" 	SELECT '4000000 AND 4999999' AS DEPARTMENTCODE,'สถาบันวิจัยและพัฒนา' AS DEPARTMENTNAME,COUNT(1) AS AMOUNT	"
				+" 	FROM V_HRM_STAFFEDU_HIGHEST V	"
				+" 	WHERE TO_NUMBER(V.DEPARTMENTCODE) BETWEEN 4000000 AND 4999999	"
				+" 		"
				+" 	UNION	"
				+" 		"
				+" 	SELECT '5000000 AND 5999999' AS DEPARTMENTCODE,'คณะครุศาสตร์' AS DEPARTMENTNAME,COUNT(1) AS AMOUNT	"
				+" 	FROM V_HRM_STAFFEDU_HIGHEST V	"
				+" 	WHERE TO_NUMBER(V.DEPARTMENTCODE) BETWEEN 5000000 AND 5999999	"
				+" 		"
				+" 	UNION	"
				+" 		"
				+" 	SELECT '6000000 AND 6999999' AS DEPARTMENTCODE,'คณะมนุษยศาสตร์และสังคมศาสตร์' AS DEPARTMENTNAME,COUNT(1) AS AMOUNT	"
				+" 	FROM V_HRM_STAFFEDU_HIGHEST V	"
				+" 	WHERE TO_NUMBER(V.DEPARTMENTCODE) BETWEEN 6000000 AND 6999999	"
				+" 		"
				+" 	UNION	"
				+" 		"
				+" 	SELECT '7000000 AND 7999999' AS DEPARTMENTCODE,'คณะวิทยาการจัดการ' AS DEPARTMENTNAME,COUNT(1) AS AMOUNT	"
				+" 	FROM V_HRM_STAFFEDU_HIGHEST V	"
				+" 	WHERE TO_NUMBER(V.DEPARTMENTCODE) BETWEEN 7000000 AND 7999999	"
				+" 		"
				+" 	UNION	"
				+" 		"
				+" 	SELECT '8000000 AND 8999999' AS DEPARTMENTCODE,'คณะวิทยาศาสตร์และเทคโนโลยี' AS DEPARTMENTNAME,COUNT(1) AS AMOUNT	"
				+" 	FROM V_HRM_STAFFEDU_HIGHEST V	"
				+" 	WHERE TO_NUMBER(V.DEPARTMENTCODE) BETWEEN 8000000 AND 8999999	"
				+" 		"
				+" 	UNION	"
				+" 		"
				+" 	SELECT '9000000 AND 9999999' AS DEPARTMENTCODE,'คณะเทคโนโลยีเกษตร' AS DEPARTMENTNAME,COUNT(1) AS AMOUNT	"
				+" 	FROM V_HRM_STAFFEDU_HIGHEST V	"
				+" 	WHERE TO_NUMBER(V.DEPARTMENTCODE) BETWEEN 9000000 AND 9999999	"
				+" 		"
				+" 	UNION	"
				+" 		"
				+" 	SELECT '10000000 AND 10999999' AS DEPARTMENTCODE,'คณะเทคโนโลยีอุตสาหกรรมและอัญมณีศาสตร์' AS DEPARTMENTNAME,COUNT(1) AS AMOUNT	"
				+" 	FROM V_HRM_STAFFEDU_HIGHEST V	"
				+" 	WHERE TO_NUMBER(V.DEPARTMENTCODE) BETWEEN 10000000 AND 10999999	"
				+" 		"
				+" 	UNION	"
				+" 		"
				+" 	SELECT '11000000 AND 11999999' AS DEPARTMENTCODE,'คณะนิติศาสตร์' AS DEPARTMENTNAME,COUNT(1) AS AMOUNT	"
				+" 	FROM V_HRM_STAFFEDU_HIGHEST V	"
				+" 	WHERE TO_NUMBER(V.DEPARTMENTCODE) BETWEEN 11000000 AND 11999999	"
				+" 		"
				+" 	UNION	"
				+" 		"
				+" 	SELECT '12000000 AND 12999999' AS DEPARTMENTCODE,'คณะวิทยาการคอมพิวเตอร์และเทคโนโลยีสารสนเทศ' AS DEPARTMENTNAME,COUNT(1) AS AMOUNT	"
				+" 	FROM V_HRM_STAFFEDU_HIGHEST V	"
				+" 	WHERE TO_NUMBER(V.DEPARTMENTCODE) BETWEEN 12000000 AND 12999999	"
				+" 		"
				+" 	UNION	"
				+" 		"
				+" 	SELECT '13000000 AND 13999999' AS DEPARTMENTCODE,'คณะนเทศศาสตร์' AS DEPARTMENTNAME,COUNT(1) AS AMOUNT	"
				+" 	FROM V_HRM_STAFFEDU_HIGHEST V	"
				+" 	WHERE TO_NUMBER(V.DEPARTMENTCODE) BETWEEN 13000000 AND 13999999	"
				+" 		"
				+" 	UNION	"
				+" 		"
				+" 	SELECT '16000000 AND 16999999' AS DEPARTMENTCODE,'คณะอัญมณีศาสตร์และประยุกต์ศิลป์' AS DEPARTMENTNAME,COUNT(1) AS AMOUNT	"
				+" 	FROM V_HRM_STAFFEDU_HIGHEST V	"
				+" 	WHERE TO_NUMBER(V.DEPARTMENTCODE) BETWEEN 16000000 AND 16999999";
		return getData(sql, con);
	}
	
	private ResultSet getFirstDrilldownSQL(Connection con, String departmentCode){
		String sql = " 	SELECT '"+departmentCode+"' AS DEPARTMENTCODE,'สำนักอธิการบดี' AS DEPARTMENTNAME,V.STAFFTYPE,SYS.BYTEDES AS STAFFTYPENAME,COUNT(1) AS AMOUNT	"
				+" 	FROM V_HRM_STAFFEDU_HIGHEST V,SYSBYTEDES SYS 	"
				+" 	WHERE TO_NUMBER(V.DEPARTMENTCODE) BETWEEN "+departmentCode
				+" 	AND V.STAFFTYPE = SYS.BYTECODE	"
				+" 	AND SYS.TABLENAME = 'STAFF'	"
				+" 	AND SYS.COLUMNNAME = 'STAFFTYPE'	"
				+" 	GROUP BY V.STAFFTYPE,SYS.BYTEDES ";
		return getData(sql, con);
	}
	
	private ResultSet getSecondDrilldownSQL(Connection con, String departmentCode,String staffType){
		String sql = " 	SELECT PRE.PREFIXNAME || S.STAFFNAME || ' ' ||S.STAFFSURNAME AS STAFFNAME,P.POSITIONNAME,D.DEPARTMENTNAME	"
				+" 	FROM STAFF S,DEPARTMENT D,POSITION P,PREFIX PRE	"
				+" 	WHERE S.STAFFTYPE = "+staffType
				+" 	AND S.DEPARTMENTID = D.DEPARTMENTID	"
				+" 	AND TO_NUMBER(D.DEPARTMENTCODE) BETWEEN "+departmentCode
				+" 	AND S.POSITIONID = P.POSITIONID	"
				+" 	AND S.PREFIXID = PRE.PREFIXID	"
				+" 	AND S.STAFFSTATUS < = 70	"
				+" 	ORDER BY D.DEPARTMENTNAME ";
		return getData(sql, con);
	}
	
	private void sendResponse(HttpServletRequest request,HttpServletResponse response,String resultJson) throws IOException{
		response.setCharacterEncoding("utf-8");
		response.setContentType("application/json;charset=utf-8");
		PrintWriter out = response.getWriter();
		out.write(resultJson);
		out.close();
	}
	
	private ResultSet getData(String sql,Connection con){
		ResultSet result = null;
		try {
			Statement statement = con.createStatement();
			result = statement.executeQuery(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}
}
